<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySQL笔记 | lilei</title>
    <meta name="generator" content="VuePress 1.8.2">
    <link rel="icon" href="/images/icons/logo.png">
    <link rel="apple-touch-icon" href="/images/icons/logo.png">
    <link rel="manifest" href="/manifest.json">
    <script type="text/javascript" src="/assets/js/load.js"></script>
    <meta name="description" content="个人网站">
    <meta name="twitter:title" content="MySQL笔记">
    <meta name="twitter:description" content="">
    <meta name="twitter:card" content="summary">
    <meta name="twitter:url" content="http://www.lileiblog.xyz/SQL/MySQL%E7%AC%94%E8%AE%B0.html">
    <meta property="og:type" content="article">
    <meta property="og:title" content="MySQL笔记">
    <meta property="og:description" content="">
    <meta property="og:url" content="http://www.lileiblog.xyz/SQL/MySQL%E7%AC%94%E8%AE%B0.html">
    <meta property="og:site_name" content="lilei">
    <meta itemprop="name" content="MySQL笔记">
    <meta itemprop="description" content="">
    <meta name="theme-color" content="#3eaf7c">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="msapplication-TileImage" content="/images/icons/logo.png">
    <meta name="msapplication-TileColor" content="#ffffff">
    <meta name="keywords" content="lilei,vue,vuepress,vuepress-theme,theme,主题,vuepress主题,blog
        ,vuepress-blog,java,python,shell,sql,golang,script,shell script,nginx,windows,去广告,androd,google drive
        ,one drive,idea,eclipse,git,小程序">
    <meta name="referrer" content="never">
    
    <link rel="preload" href="/assets/css/0.styles.8932179b.css" as="style"><link rel="preload" href="/assets/js/app.269009d6.js" as="script"><link rel="preload" href="/assets/js/3.974b9720.js" as="script"><link rel="preload" href="/assets/js/53.3a9bd5fb.js" as="script"><link rel="prefetch" href="/assets/js/10.5915e99b.js"><link rel="prefetch" href="/assets/js/11.ff732762.js"><link rel="prefetch" href="/assets/js/12.8bb86d91.js"><link rel="prefetch" href="/assets/js/13.18bde5b0.js"><link rel="prefetch" href="/assets/js/14.314fae34.js"><link rel="prefetch" href="/assets/js/15.0a4ff48f.js"><link rel="prefetch" href="/assets/js/16.ffaf1503.js"><link rel="prefetch" href="/assets/js/17.49a31a6e.js"><link rel="prefetch" href="/assets/js/18.7e6443bf.js"><link rel="prefetch" href="/assets/js/19.6880cc9a.js"><link rel="prefetch" href="/assets/js/20.1fb3f255.js"><link rel="prefetch" href="/assets/js/21.0cd39eea.js"><link rel="prefetch" href="/assets/js/22.392d85cb.js"><link rel="prefetch" href="/assets/js/23.f50adb15.js"><link rel="prefetch" href="/assets/js/24.43e324e1.js"><link rel="prefetch" href="/assets/js/25.e3663205.js"><link rel="prefetch" href="/assets/js/26.da141fb8.js"><link rel="prefetch" href="/assets/js/27.6c05235a.js"><link rel="prefetch" href="/assets/js/28.c124245a.js"><link rel="prefetch" href="/assets/js/29.d185870d.js"><link rel="prefetch" href="/assets/js/30.8587d422.js"><link rel="prefetch" href="/assets/js/31.dc382b56.js"><link rel="prefetch" href="/assets/js/32.20ba32e9.js"><link rel="prefetch" href="/assets/js/33.6ecd7de9.js"><link rel="prefetch" href="/assets/js/34.2b91c13a.js"><link rel="prefetch" href="/assets/js/35.66ed0733.js"><link rel="prefetch" href="/assets/js/36.ce7bca84.js"><link rel="prefetch" href="/assets/js/37.d00bb5ac.js"><link rel="prefetch" href="/assets/js/38.c3ecbb6f.js"><link rel="prefetch" href="/assets/js/39.308f4175.js"><link rel="prefetch" href="/assets/js/4.520f6ecc.js"><link rel="prefetch" href="/assets/js/40.f4b98bc2.js"><link rel="prefetch" href="/assets/js/41.95fae64f.js"><link rel="prefetch" href="/assets/js/42.fe4f877b.js"><link rel="prefetch" href="/assets/js/43.ca8f222e.js"><link rel="prefetch" href="/assets/js/44.4d4ee79e.js"><link rel="prefetch" href="/assets/js/45.79178b33.js"><link rel="prefetch" href="/assets/js/46.c3817d0b.js"><link rel="prefetch" href="/assets/js/47.b8252443.js"><link rel="prefetch" href="/assets/js/48.df9d5d3b.js"><link rel="prefetch" href="/assets/js/49.d812fdb2.js"><link rel="prefetch" href="/assets/js/5.f1a24a16.js"><link rel="prefetch" href="/assets/js/50.55cfcaa8.js"><link rel="prefetch" href="/assets/js/51.b8608b02.js"><link rel="prefetch" href="/assets/js/52.52c5e00d.js"><link rel="prefetch" href="/assets/js/54.a4240c13.js"><link rel="prefetch" href="/assets/js/55.01c4b876.js"><link rel="prefetch" href="/assets/js/56.f29549a3.js"><link rel="prefetch" href="/assets/js/57.01438a04.js"><link rel="prefetch" href="/assets/js/58.29bb4626.js"><link rel="prefetch" href="/assets/js/59.e8b89408.js"><link rel="prefetch" href="/assets/js/6.8c478b39.js"><link rel="prefetch" href="/assets/js/60.bedb5381.js"><link rel="prefetch" href="/assets/js/61.bdfbe584.js"><link rel="prefetch" href="/assets/js/62.cb825d1a.js"><link rel="prefetch" href="/assets/js/63.a9516572.js"><link rel="prefetch" href="/assets/js/64.aa854ed7.js"><link rel="prefetch" href="/assets/js/65.285f883d.js"><link rel="prefetch" href="/assets/js/66.12849f37.js"><link rel="prefetch" href="/assets/js/67.c18b00e4.js"><link rel="prefetch" href="/assets/js/68.fa59daa9.js"><link rel="prefetch" href="/assets/js/69.e9d5dda4.js"><link rel="prefetch" href="/assets/js/7.cfb0d0ea.js"><link rel="prefetch" href="/assets/js/70.db126cce.js"><link rel="prefetch" href="/assets/js/71.6acb04cd.js"><link rel="prefetch" href="/assets/js/72.7ff0ab11.js"><link rel="prefetch" href="/assets/js/73.9a66da79.js"><link rel="prefetch" href="/assets/js/74.0a38a030.js"><link rel="prefetch" href="/assets/js/75.7b32dbb3.js"><link rel="prefetch" href="/assets/js/8.cb9c08e5.js"><link rel="prefetch" href="/assets/js/9.ad652cfe.js"><link rel="prefetch" href="/assets/js/vendors~flowchart.110a1065.js">
    <link rel="stylesheet" href="/assets/css/0.styles.8932179b.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/images/icons/logo.png" alt="lilei" class="logo"> <span class="site-name can-hide">lilei</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/Form/" class="nav-link">
  Form
</a></div><div class="nav-item"><a href="/IDE/" class="nav-link">
  IDE
</a></div><div class="nav-item"><a href="/Java/" class="nav-link">
  Java
</a></div><div class="nav-item"><a href="/Other/" class="nav-link">
  Other
</a></div><div class="nav-item"><a href="/Python/" class="nav-link">
  Python
</a></div><div class="nav-item"><a href="/SQL/" class="nav-link router-link-active">
  SQL
</a></div><div class="nav-item"><a href="/Shell/" class="nav-link">
  Shell
</a></div><div class="nav-item"><a href="/System/" class="nav-link">
  System
</a></div> <a href="https://github.com/woytu/notes-vuepress" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="/Form/" class="nav-link">
  Form
</a></div><div class="nav-item"><a href="/IDE/" class="nav-link">
  IDE
</a></div><div class="nav-item"><a href="/Java/" class="nav-link">
  Java
</a></div><div class="nav-item"><a href="/Other/" class="nav-link">
  Other
</a></div><div class="nav-item"><a href="/Python/" class="nav-link">
  Python
</a></div><div class="nav-item"><a href="/SQL/" class="nav-link router-link-active">
  SQL
</a></div><div class="nav-item"><a href="/Shell/" class="nav-link">
  Shell
</a></div><div class="nav-item"><a href="/System/" class="nav-link">
  System
</a></div> <a href="https://github.com/woytu/notes-vuepress" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group depth-0"><p class="sidebar-heading open"><span>SQL</span> <!----></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/SQL/" aria-current="page" class="sidebar-link">SQL</a></li><li><a href="/SQL/MySQL事件.html" class="sidebar-link">MySQL事件</a></li><li><a href="/SQL/MySQL备份恢复数据.html" class="sidebar-link">MySQL备份恢复数据</a></li><li><a href="/SQL/MySQL存储过程.html" class="sidebar-link">MySQL存储过程</a></li><li><a href="/SQL/MySQL安装配置.html" class="sidebar-link">MySQL安装配置</a></li><li><a href="/SQL/MySQL常见问题.html" class="sidebar-link">MySQL常见问题</a></li><li><a href="/SQL/MySQL笔记.html" class="active sidebar-link">MySQL笔记</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/SQL/MySQL笔记.html#flag" class="sidebar-link">Flag</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL笔记.html#常用语句" class="sidebar-link">常用语句</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL笔记.html#数据库信息" class="sidebar-link">数据库信息</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL笔记.html#时间函数" class="sidebar-link">时间函数</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL笔记.html#用户管理" class="sidebar-link">用户管理</a></li><li class="sidebar-sub-header"><a href="/SQL/MySQL笔记.html#安装后修改密码" class="sidebar-link">安装后修改密码</a></li></ul></li><li><a href="/SQL/mysql-udf安装.html" class="sidebar-link">mysql-udf安装</a></li></ul></section></li></ul> </aside> <main class="page"> <div class="theme-default-content content__default"><h1 id="mysql笔记"><a href="#mysql笔记" class="header-anchor">#</a> MySQL笔记</h1> <p></p><div class="table-of-contents"><ul><li><a href="#flag">Flag</a></li><li><a href="#常用语句">常用语句</a><ul><li><a href="#命令行">命令行</a></li><li><a href="#随机取值">随机取值</a></li><li><a href="#随机插入">随机插入</a></li><li><a href="#查询分组中某字段最大值">查询分组中某字段最大值</a></li><li><a href="#删除重复值">删除重复值</a></li><li><a href="#查询数据库中的存储过程">查询数据库中的存储过程</a></li><li><a href="#事务隔离级别修改">事务隔离级别修改</a></li></ul></li><li><a href="#数据库信息">数据库信息</a><ul><li><a href="#查看数据库详细信息">查看数据库详细信息</a></li><li><a href="#查看所有数据库信息">查看所有数据库信息</a></li><li><a href="#查看一个数据库占用空间的大小">查看一个数据库占用空间的大小</a></li><li><a href="#查看一个表占用空间的大小">查看一个表占用空间的大小</a></li></ul></li><li><a href="#时间函数">时间函数</a><ul><li><a href="#获取当前日期时间">获取当前日期时间</a></li><li><a href="#获得当前日期">获得当前日期</a></li><li><a href="#获得当前时间time函数">获得当前时间time函数</a></li><li><a href="#获得当前utc日期时间函数">获得当前UTC日期时间函数</a></li><li><a href="#格式化日期">格式化日期</a></li><li><a href="#生成18位uuid加14位精确到秒的时间">生成18位uuid加14位精确到秒的时间</a></li><li><a href="#日期增加或者减去一个时间间隔">日期增加或者减去一个时间间隔</a></li><li><a href="#日期时间相减">日期时间相减</a></li><li><a href="#时间戳">时间戳</a></li><li><a href="#日期时间选取">日期时间选取</a></li><li><a href="#返回星期和月份名称">返回星期和月份名称</a></li></ul></li><li><a href="#用户管理">用户管理</a><ul><li><a href="#查看授权">查看授权</a></li><li><a href="#创建用户并授权">创建用户并授权</a></li><li><a href="#撤销用户权限">撤销用户权限</a></li><li><a href="#删除账户及权限">删除账户及权限</a></li></ul></li><li><a href="#安装后修改密码">安装后修改密码</a><ul><li><a href="#使用默认密码进入修改密码">使用默认密码进入修改密码</a></li><li><a href="#修改配置修改密码">修改配置修改密码</a></li></ul></li></ul></div><p></p> <h2 id="flag"><a href="#flag" class="header-anchor">#</a> Flag</h2> <ul><li><a href="https://github.com/guodongxiaren/MySQL-docs" target="_blank" rel="noopener noreferrer">https://github.com/guodongxiaren/MySQL-docs<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul> <ul><li><a href="https://blog.csdn.net/sinat_19351993/article/details/47169789" target="_blank" rel="noopener noreferrer">MySQL系列——MySQL实现序列（Sequence）效果<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul> <h2 id="常用语句"><a href="#常用语句" class="header-anchor">#</a> 常用语句</h2> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- 根据表注释查找对应的表</span>
<span class="token keyword">SELECT</span> TABLE_NAME 表名<span class="token punctuation">,</span>TABLE_COMMENT <span class="token string">'表注解'</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">TABLES</span>
 <span class="token keyword">WHERE</span> TABLE_SCHEMA <span class="token operator">=</span> <span class="token string">'数据库名'</span> <span class="token operator">AND</span> TABLE_COMMENT  <span class="token operator">LIKE</span> <span class="token string">'%注释关键词%'</span><span class="token punctuation">;</span>
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">-- 插入或替换 若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。</span>
<span class="token keyword">REPLACE</span> <span class="token keyword">INTO</span> students <span class="token punctuation">(</span>id<span class="token punctuation">,</span> class_id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> gender<span class="token punctuation">,</span> score<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'小明'</span><span class="token punctuation">,</span> <span class="token string">'F'</span><span class="token punctuation">,</span> <span class="token number">99</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 插入或更新 upsert</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> students <span class="token punctuation">(</span>id<span class="token punctuation">,</span> class_id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> gender<span class="token punctuation">,</span> score<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'小明'</span><span class="token punctuation">,</span> <span class="token string">'F'</span><span class="token punctuation">,</span> <span class="token number">99</span><span class="token punctuation">)</span> <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> name<span class="token operator">=</span><span class="token string">'小明'</span><span class="token punctuation">,</span> gender<span class="token operator">=</span><span class="token string">'F'</span><span class="token punctuation">,</span> score<span class="token operator">=</span><span class="token number">99</span><span class="token punctuation">;</span>
<span class="token comment">-- 插入或忽略</span>
<span class="token keyword">INSERT</span> <span class="token keyword">IGNORE</span> <span class="token keyword">INTO</span> students <span class="token punctuation">(</span>id<span class="token punctuation">,</span> class_id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> gender<span class="token punctuation">,</span> score<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'小明'</span><span class="token punctuation">,</span> <span class="token string">'F'</span><span class="token punctuation">,</span> <span class="token number">99</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="命令行"><a href="#命令行" class="header-anchor">#</a> 命令行</h3> <blockquote><p>可以用shell脚本操作mysql数据库，使用mysql的<code>-e</code>参数可以执行各种sql的(创建，删除，增，删，改、查)等各种操作。</p></blockquote> <div class="language-bash extra-class"><pre class="language-bash"><code><span class="token shebang important">#!/bin/bash</span>

<span class="token comment"># 数据库信息</span>
<span class="token assign-left variable"><span class="token environment constant">HOSTNAME</span></span><span class="token operator">=</span><span class="token string">&quot;192.168.111.84&quot;</span>
<span class="token assign-left variable">PORT</span><span class="token operator">=</span><span class="token string">&quot;3306&quot;</span>
<span class="token assign-left variable">USERNAME</span><span class="token operator">=</span><span class="token string">&quot;root&quot;</span>
<span class="token assign-left variable">PASSWORD</span><span class="token operator">=</span><span class="token string">&quot;&quot;</span>
<span class="token comment"># 数据库名称</span>
<span class="token assign-left variable">DBNAME</span><span class="token operator">=</span><span class="token string">&quot;test_db_test&quot;</span>
<span class="token comment"># 数据库中表的名称</span>
<span class="token assign-left variable">TABLENAME</span><span class="token operator">=</span><span class="token string">&quot;test_table_test&quot;</span>

<span class="token comment"># 查询数据库 </span>
mysql -hlocalhost -P3306 -uroot -pabc123 -e <span class="token string">&quot;show databases;&quot;</span>

<span class="token comment"># 创建数据库</span>
<span class="token assign-left variable">create_db_sql</span><span class="token operator">=</span><span class="token string">&quot;create database IF NOT EXISTS <span class="token variable">${DBNAME}</span>&quot;</span>
mysql -h<span class="token variable">${<span class="token environment constant">HOSTNAME</span>}</span>  -P<span class="token variable">${PORT}</span>  -u<span class="token variable">${USERNAME}</span> -p<span class="token variable">${PASSWORD}</span> -e<span class="token string">&quot;<span class="token variable">${create_db_sql}</span>&quot;</span>
 
<span class="token comment"># 创建表</span>
<span class="token assign-left variable">create_table_sql</span><span class="token operator">=</span><span class="token string">&quot;create table IF NOT EXISTS <span class="token variable">${TABLENAME}</span> (name varchar(20), id int(11) default 0)&quot;</span>
mysql -h<span class="token variable">${<span class="token environment constant">HOSTNAME</span>}</span>  -P<span class="token variable">${PORT}</span>  -u<span class="token variable">${USERNAME}</span> -p<span class="token variable">${PASSWORD}</span> <span class="token variable">${DBNAME}</span> -e<span class="token string">&quot;<span class="token variable">${create_table_sql}</span>&quot;</span>

<span class="token comment"># 插入数据</span>
<span class="token assign-left variable">insert_sql</span><span class="token operator">=</span><span class="token string">&quot;insert into <span class="token variable">${TABLENAME}</span> values('billchen',2)&quot;</span>
mysql -h<span class="token variable">${<span class="token environment constant">HOSTNAME</span>}</span>  -P<span class="token variable">${PORT}</span>  -u<span class="token variable">${USERNAME}</span> -p<span class="token variable">${PASSWORD}</span> <span class="token variable">${DBNAME}</span> -e<span class="token string">&quot;<span class="token variable">${insert_sql}</span>&quot;</span>
 
<span class="token comment"># 查询</span>
<span class="token assign-left variable">select_sql</span><span class="token operator">=</span><span class="token string">&quot;select * from <span class="token variable">${TABLENAME}</span>&quot;</span>
mysql -h<span class="token variable">${<span class="token environment constant">HOSTNAME</span>}</span>  -P<span class="token variable">${PORT}</span>  -u<span class="token variable">${USERNAME}</span> -p<span class="token variable">${PASSWORD}</span> <span class="token variable">${DBNAME}</span> -e<span class="token string">&quot;<span class="token variable">${select_sql}</span>&quot;</span>
 
<span class="token comment"># 更新数据</span>
<span class="token assign-left variable">update_sql</span><span class="token operator">=</span><span class="token string">&quot;update <span class="token variable">${TABLENAME}</span> set id=3&quot;</span>
mysql -h<span class="token variable">${<span class="token environment constant">HOSTNAME</span>}</span>  -P<span class="token variable">${PORT}</span>  -u<span class="token variable">${USERNAME}</span> -p<span class="token variable">${PASSWORD}</span> <span class="token variable">${DBNAME}</span> -e<span class="token string">&quot;<span class="token variable">${update_sql}</span>&quot;</span>
mysql -h<span class="token variable">${<span class="token environment constant">HOSTNAME</span>}</span>  -P<span class="token variable">${PORT}</span>  -u<span class="token variable">${USERNAME}</span> -p<span class="token variable">${PASSWORD}</span> <span class="token variable">${DBNAME}</span> -e<span class="token string">&quot;<span class="token variable">${select_sql}</span>&quot;</span>
 
<span class="token comment"># 删除数据</span>
<span class="token assign-left variable">delete_sql</span><span class="token operator">=</span><span class="token string">&quot;delete from <span class="token variable">${TABLENAME}</span>&quot;</span>
mysql -h<span class="token variable">${<span class="token environment constant">HOSTNAME</span>}</span>  -P<span class="token variable">${PORT}</span>  -u<span class="token variable">${USERNAME}</span> -p<span class="token variable">${PASSWORD}</span> <span class="token variable">${DBNAME}</span> -e<span class="token string">&quot;<span class="token variable">${delete_sql}</span>&quot;</span>
mysql -h<span class="token variable">${<span class="token environment constant">HOSTNAME</span>}</span>  -P<span class="token variable">${PORT}</span>  -u<span class="token variable">${USERNAME}</span> -p<span class="token variable">${PASSWORD}</span> <span class="token variable">${DBNAME}</span> -e<span class="token string">&quot;<span class="token variable">${select_sql}</span>&quot;</span>

</code></pre></div><h3 id="随机取值"><a href="#随机取值" class="header-anchor">#</a> 随机取值</h3> <blockquote><p>先根据rand()和id获取一遍随机数，再跟原表关联，最后取出值，因为是随机取，
所以你的id值越平均，数据量越大，最后limit之前获取的id值就会越接近整表数据量的一半，limit取到的结果也越接近。</p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span>table1<span class="token punctuation">`</span> <span class="token keyword">AS</span> t1 <span class="token keyword">JOIN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">ROUND</span><span class="token punctuation">(</span>RAND<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token keyword">FROM</span> <span class="token punctuation">`</span>table1<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> id<span class="token punctuation">)</span> <span class="token keyword">AS</span> t2 <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>id <span class="token operator">&gt;=</span> t2<span class="token punctuation">.</span>id <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> t1<span class="token punctuation">.</span>id <span class="token keyword">ASC</span> <span class="token keyword">LIMIT</span> <span class="token number">1</span>
</code></pre></div><ul><li>最大减去最小值乘以随机值获取随机数</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token keyword">USER</span> <span class="token keyword">WHERE</span> id <span class="token operator">&gt;=</span> <span class="token punctuation">(</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span> id <span class="token punctuation">)</span> <span class="token keyword">FROM</span> <span class="token keyword">USER</span> <span class="token punctuation">)</span> <span class="token operator">-</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span> id <span class="token punctuation">)</span> <span class="token keyword">FROM</span> <span class="token keyword">USER</span> <span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token operator">*</span> RAND<span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span> id <span class="token punctuation">)</span> <span class="token keyword">FROM</span> <span class="token keyword">USER</span> <span class="token punctuation">)</span> <span class="token keyword">LIMIT</span> <span class="token number">1</span>
</code></pre></div><blockquote><p><code>order by rand()</code>效率极低，采用JOIN的语法比直接在WHERE中使用函数效率还要高很多</p></blockquote> <h3 id="随机插入"><a href="#随机插入" class="header-anchor">#</a> 随机插入</h3> <ul><li>连续有限队列随机取</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">update</span> table1 <span class="token keyword">set</span> a <span class="token operator">=</span> <span class="token punctuation">(</span>floor<span class="token punctuation">(</span>rand<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">4</span><span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token number">3</span><span class="token punctuation">)</span> <span class="token keyword">where</span> b <span class="token operator">=</span> <span class="token number">84</span><span class="token punctuation">;</span>
</code></pre></div><ul><li>非连续有限队列随机取</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">update</span> table1 <span class="token keyword">set</span> a <span class="token operator">=</span> elt<span class="token punctuation">(</span>floor<span class="token punctuation">(</span>rand<span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">4</span><span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">7</span><span class="token punctuation">,</span> <span class="token number">11</span><span class="token punctuation">,</span> <span class="token number">13</span><span class="token punctuation">)</span> <span class="token keyword">where</span> b <span class="token operator">=</span> <span class="token number">84</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="查询分组中某字段最大值"><a href="#查询分组中某字段最大值" class="header-anchor">#</a> 查询分组中某字段最大值</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> 别名<span class="token punctuation">.</span>字段 <span class="token keyword">HAVING</span> <span class="token function">MAX</span><span class="token punctuation">(</span>别名<span class="token punctuation">.</span>字段<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 因为`group by`后取的一条数据默认是按照主键id排序后的第一条，所以对表先排序，然后再分组</span>
<span class="token keyword">SELECT</span> 别名<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 表名 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> 字段 <span class="token keyword">DESC</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> 别名 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> 别名<span class="token punctuation">.</span>字段<span class="token punctuation">;</span>
</code></pre></div><h3 id="删除重复值"><a href="#删除重复值" class="header-anchor">#</a> 删除重复值</h3> <p><strong>查询单个字段</strong></p> <ul><li>方式一</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> test <span class="token keyword">WHERE</span> id <span class="token operator">NOT</span> <span class="token operator">IN</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token keyword">FROM</span> images <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> name<span class="token punctuation">)</span>
</code></pre></div><ul><li>方式二</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> test <span class="token keyword">WHERE</span> name 
<span class="token operator">IN</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> name <span class="token keyword">FROM</span> test <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> name <span class="token keyword">HAVING</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>name<span class="token punctuation">)</span><span class="token operator">&gt;</span><span class="token number">1</span><span class="token punctuation">)</span> 
<span class="token operator">AND</span> id <span class="token operator">NOT</span> <span class="token operator">IN</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token keyword">FROM</span> test <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> name <span class="token keyword">HAVING</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>name<span class="token punctuation">)</span><span class="token operator">&gt;</span><span class="token number">1</span><span class="token punctuation">)</span>
</code></pre></div><h3 id="查询数据库中的存储过程"><a href="#查询数据库中的存储过程" class="header-anchor">#</a> 查询数据库中的存储过程</h3> <div class="language-sql extra-class"><pre class="language-sql"><code>selectnamefrom mysql<span class="token punctuation">.</span><span class="token keyword">proc</span> <span class="token keyword">where</span> db <span class="token operator">=</span> <span class="token string">'your_db_name'</span> andtype<span class="token operator">=</span> <span class="token string">'PROCEDURE'</span><span class="token punctuation">;</span>

<span class="token keyword">show</span> <span class="token keyword">procedure</span> <span class="token keyword">status</span><span class="token punctuation">;</span>

<span class="token comment">-- 查看存储过程或函数的创建代码</span>
<span class="token keyword">show</span> <span class="token keyword">create</span> <span class="token keyword">procedure</span> proc_name<span class="token punctuation">;</span>
<span class="token keyword">show</span> <span class="token keyword">create</span> <span class="token keyword">function</span> func_name<span class="token punctuation">;</span>
</code></pre></div><h3 id="事务隔离级别修改"><a href="#事务隔离级别修改" class="header-anchor">#</a> 事务隔离级别修改</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SET</span> <span class="token punctuation">[</span><span class="token keyword">SESSION</span> <span class="token operator">|</span> <span class="token keyword">GLOBAL</span><span class="token punctuation">]</span> <span class="token keyword">TRANSACTION</span> <span class="token keyword">ISOLATION</span> <span class="token keyword">LEVEL</span> <span class="token punctuation">[</span><span class="token keyword">READ</span> <span class="token keyword">UNCOMMITTED</span> <span class="token operator">|</span> <span class="token keyword">READ</span> <span class="token keyword">COMMITTED</span> <span class="token operator">|</span> <span class="token keyword">REPEATABLE</span> <span class="token keyword">READ</span> <span class="token operator">|</span> <span class="token keyword">SERIALIZABLE</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
</code></pre></div><table><thead><tr><th>代码</th> <th>说明</th></tr></thead> <tbody><tr><td>SESSION</td> <td>当前会话</td></tr> <tr><td>GLOBAL</td> <td>全局</td></tr> <tr><td>READ UNCOMMITTED</td> <td>读取未提交内容</td></tr> <tr><td>READ COMMITTED</td> <td>读取提交内容</td></tr> <tr><td>REPEATABLE READ</td> <td>可重读</td></tr> <tr><td>SERIALIZABLE</td> <td>可串行化</td></tr></tbody></table> <h2 id="数据库信息"><a href="#数据库信息" class="header-anchor">#</a> 数据库信息</h2> <h3 id="查看数据库详细信息"><a href="#查看数据库详细信息" class="header-anchor">#</a> 查看数据库详细信息</h3> <ul><li><code>information_schema</code>数据库记录每个表和数据库的详细信息，在该库中有一个TABLES表：</li></ul> <table><thead><tr><th>字段</th> <th>说明</th></tr></thead> <tbody><tr><td>TABLE_SCHEMA</td> <td>数据库名</td></tr> <tr><td>TABLE_NAME</td> <td>表名</td></tr> <tr><td>ENGINE</td> <td>所使用的存储引擎</td></tr> <tr><td>TABLES_ROWS</td> <td>记录数</td></tr> <tr><td>DATA_LENGTH</td> <td>数据大小</td></tr> <tr><td>INDEX_LENGTH</td> <td>索引大小</td></tr></tbody></table> <blockquote><p>其他字段请参考MySQL的手册，我们只需要了解这几个就足够了。</p></blockquote> <h3 id="查看所有数据库信息"><a href="#查看所有数据库信息" class="header-anchor">#</a> 查看所有数据库信息</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
	table_schema <span class="token keyword">AS</span> <span class="token string">'数据库'</span><span class="token punctuation">,</span>
	<span class="token function">sum</span><span class="token punctuation">(</span> table_rows <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'总行数'</span><span class="token punctuation">,</span>
	<span class="token function">sum</span><span class="token punctuation">(</span> <span class="token keyword">TRUNCATE</span> <span class="token punctuation">(</span> data_length <span class="token operator">/</span> <span class="token number">1024</span> <span class="token operator">/</span> <span class="token number">1024</span><span class="token punctuation">,</span> <span class="token number">2</span> <span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'数据容量(MB)'</span><span class="token punctuation">,</span>
	<span class="token function">sum</span><span class="token punctuation">(</span> <span class="token keyword">TRUNCATE</span> <span class="token punctuation">(</span> index_length <span class="token operator">/</span> <span class="token number">1024</span> <span class="token operator">/</span> <span class="token number">1024</span><span class="token punctuation">,</span> <span class="token number">2</span> <span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'索引容量(MB)'</span> 
<span class="token keyword">FROM</span>
	information_schema<span class="token punctuation">.</span><span class="token keyword">TABLES</span> 
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span>
	table_schema 
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>
	<span class="token function">sum</span><span class="token punctuation">(</span> table_rows <span class="token punctuation">)</span> <span class="token keyword">DESC</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="查看一个数据库占用空间的大小"><a href="#查看一个数据库占用空间的大小" class="header-anchor">#</a> 查看一个数据库占用空间的大小</h3> <blockquote><p>那就相当于是数据大小(<code>DATA_LENGTH</code>)+索引大小(<code>INDEX_LENGTH</code>)即可</p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
	table_schema <span class="token keyword">AS</span> <span class="token string">'数据库'</span><span class="token punctuation">,</span>
	table_name <span class="token keyword">AS</span> <span class="token string">'表名'</span><span class="token punctuation">,</span>
	table_rows <span class="token keyword">AS</span> <span class="token string">'行数'</span><span class="token punctuation">,</span>
	<span class="token keyword">TRUNCATE</span> <span class="token punctuation">(</span> data_length <span class="token operator">/</span> <span class="token number">1024</span> <span class="token operator">/</span> <span class="token number">1024</span><span class="token punctuation">,</span> <span class="token number">2</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'数据容量(MB)'</span><span class="token punctuation">,</span>
	<span class="token keyword">TRUNCATE</span> <span class="token punctuation">(</span> index_length <span class="token operator">/</span> <span class="token number">1024</span> <span class="token operator">/</span> <span class="token number">1024</span><span class="token punctuation">,</span> <span class="token number">2</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'索引容量(MB)'</span> 
<span class="token keyword">FROM</span>
	information_schema<span class="token punctuation">.</span><span class="token keyword">TABLES</span> 
<span class="token keyword">WHERE</span>
	table_schema <span class="token operator">=</span> <span class="token string">'数据库'</span> 
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>
	table_rows <span class="token keyword">DESC</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="查看一个表占用空间的大小"><a href="#查看一个表占用空间的大小" class="header-anchor">#</a> 查看一个表占用空间的大小</h3> <blockquote><p>那就相当于是数据大小(<code>DATA_LENGTH</code>)+索引大小(<code>INDEX_LENGTH</code>)即可</p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
	table_schema <span class="token keyword">AS</span> <span class="token string">'数据库'</span><span class="token punctuation">,</span>
	table_name <span class="token keyword">AS</span> <span class="token string">'表名'</span><span class="token punctuation">,</span>
	table_rows <span class="token keyword">AS</span> <span class="token string">'行数'</span><span class="token punctuation">,</span>
	<span class="token keyword">TRUNCATE</span> <span class="token punctuation">(</span> data_length <span class="token operator">/</span> <span class="token number">1024</span> <span class="token operator">/</span> <span class="token number">1024</span><span class="token punctuation">,</span> <span class="token number">2</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'数据容量(MB)'</span><span class="token punctuation">,</span>
	<span class="token keyword">TRUNCATE</span> <span class="token punctuation">(</span> index_length <span class="token operator">/</span> <span class="token number">1024</span> <span class="token operator">/</span> <span class="token number">1024</span><span class="token punctuation">,</span> <span class="token number">2</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'索引容量(MB)'</span> 
<span class="token keyword">FROM</span>
	information_schema<span class="token punctuation">.</span><span class="token keyword">TABLES</span> 
<span class="token keyword">WHERE</span>
	table_schema <span class="token operator">=</span> <span class="token string">'数据库'</span> 
	<span class="token operator">AND</span> TABLE_NAME <span class="token operator">=</span> <span class="token string">'表名'</span> 
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>
	table_rows <span class="token keyword">DESC</span><span class="token punctuation">;</span>
</code></pre></div><h2 id="时间函数"><a href="#时间函数" class="header-anchor">#</a> 时间函数</h2> <h3 id="获取当前日期时间"><a href="#获取当前日期时间" class="header-anchor">#</a> 获取当前日期时间</h3> <blockquote><p>获得当前日期+时间（date+time）函数</p></blockquote> <ul><li><code>localtime()</code></li> <li><code>localtimestamp()</code></li> <li><code>now()</code></li> <li><code>current_timestamp()</code></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">CURRENT_TIMESTAMP</span> { <span class="token operator">+</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token punctuation">[</span><span class="token keyword">HOUR</span><span class="token operator">|</span><span class="token keyword">MONTH</span><span class="token operator">|</span>WEEK<span class="token operator">|</span><span class="token keyword">DAY</span><span class="token operator">|</span><span class="token keyword">MINUTE</span><span class="token operator">|</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span> }
</code></pre></div><ul><li><code>INTERVAL</code>时间单位</li></ul> <table><thead><tr><th>unit</th> <th>说明</th></tr></thead> <tbody><tr><td>YEAR</td> <td>年</td></tr> <tr><td>QUARTER</td> <td>季度</td></tr> <tr><td>MONTH</td> <td>月</td></tr> <tr><td>DAY</td> <td>天</td></tr> <tr><td>HOUR</td> <td>时</td></tr> <tr><td>MINUTE</td> <td>分</td></tr> <tr><td>WEEK</td> <td>周</td></tr> <tr><td>SECOND</td> <td>秒</td></tr> <tr><td>YEAR_MONTH</td> <td>年:月</td></tr> <tr><td>DAY_HOUR</td> <td>日:时</td></tr> <tr><td>DAY_MINUTE</td> <td>日:分</td></tr> <tr><td>DAY_SECOND</td> <td>日:秒</td></tr> <tr><td>HOUR_MINUTE</td> <td>时:分</td></tr> <tr><td>HOUR_SECOND</td> <td>时:秒</td></tr> <tr><td>MINUTE_SECOND</td> <td>分:秒</td></tr></tbody></table> <ul><li><code>sysdate()</code> 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。</li></ul> <h3 id="获得当前日期"><a href="#获得当前日期" class="header-anchor">#</a> 获得当前日期</h3> <ul><li><code>curdate()</code>下面的两个时间函数同阶
<ul><li><code>current_date()</code></li> <li><code>current_date</code></li></ul></li></ul> <h3 id="获得当前时间time函数"><a href="#获得当前时间time函数" class="header-anchor">#</a> 获得当前时间time函数</h3> <ul><li><code>curtime()</code>下面的两个时间函数同阶
<ul><li><code>current_time()</code></li> <li><code>current_time</code></li></ul></li></ul> <h3 id="获得当前utc日期时间函数"><a href="#获得当前utc日期时间函数" class="header-anchor">#</a> 获得当前UTC日期时间函数</h3> <ul><li><code>utc_date()</code></li> <li><code>utc_time()</code></li> <li><code>utc_timestamp()</code></li></ul> <h3 id="格式化日期"><a href="#格式化日期" class="header-anchor">#</a> 格式化日期</h3> <blockquote><p><code>DATE_FORMAT(date, format)</code></p></blockquote> <h3 id="生成18位uuid加14位精确到秒的时间"><a href="#生成18位uuid加14位精确到秒的时间" class="header-anchor">#</a> 生成18位uuid加14位精确到秒的时间</h3> <div class="language-sql extra-class"><pre class="language-sql"><code>CONCAT<span class="token punctuation">(</span><span class="token keyword">LEFT</span><span class="token punctuation">(</span><span class="token keyword">REPLACE</span><span class="token punctuation">(</span>UUID<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'-'</span><span class="token punctuation">,</span><span class="token string">''</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token number">18</span><span class="token punctuation">)</span><span class="token punctuation">,</span>DATE_FORMAT<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'%Y%m%d%H%i%S'</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
</code></pre></div><table><thead><tr><th>格式</th> <th>说明</th></tr></thead> <tbody><tr><td><code>%%</code></td> <td>直接值“%”</td></tr> <tr><td><code>%a</code></td> <td>一周中每一天名称的缩写（Sun, Mon, ..., Sat）</td></tr> <tr><td><code>%b</code></td> <td>缩写的月名（ January, February,...., December）</td></tr> <tr><td><code>%c</code></td> <td>数字表示的月份（1, 2, ...., 12）</td></tr> <tr><td><code>%d</code></td> <td>两位数字表示月中的天数（00, 01,..., 31）</td></tr> <tr><td><code>%D</code></td> <td>英文后缀表示月中的天数（1st, 2nd, 3rd,...）</td></tr> <tr><td><code>%e</code></td> <td>数字形式表示月中的天数（1, 2， ..., 31）</td></tr> <tr><td><code>%H</code></td> <td>两位数字形式的小时，24 小时（00,01, ..., 23）</td></tr> <tr><td><code>%h</code></td> <td>两位数字形式的小时，12 小时（01,02, ..., 12）</td></tr> <tr><td><code>%I</code>, <code>%i</code></td> <td>两位数字形式的分（ 00,01, ..., 59）</td></tr> <tr><td><code>%j</code></td> <td>以三位数字表示年中的天数（ 001, 002, ..., 366）</td></tr> <tr><td><code>%k</code></td> <td>数字形式的小时，24 小时（0,1, ..., 23）</td></tr> <tr><td><code>%l</code></td> <td>数字形式的小时，12 小时（1, 2, ..., 12）</td></tr> <tr><td><code>%M</code></td> <td>月名（January, February, ..., December）</td></tr> <tr><td><code>%m</code></td> <td>两位数字表示的月份（01, 02, ..., 12）</td></tr> <tr><td><code>%p</code></td> <td>AM或PM</td></tr> <tr><td><code>%r</code></td> <td>12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）</td></tr> <tr><td><code>%S</code>, <code>%s</code></td> <td>两位数字形式的秒（ 00,01, ..., 59）</td></tr> <tr><td><code>%T</code></td> <td>24 小时的时间形式（hh:mm:ss）</td></tr> <tr><td><code>%U</code></td> <td>周（0, 1, 52），其中Sunday 为周中的第一天</td></tr> <tr><td><code>%u</code></td> <td>周（0, 1, 52），其中Monday 为周中的第一天</td></tr> <tr><td><code>%W</code></td> <td>一周中每一天的名称（Sunday, Monday, ..., Saturday）</td></tr> <tr><td><code>%w</code></td> <td>以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, ..., 6=Saturday）</td></tr> <tr><td><code>%Y</code></td> <td>四位数字表示的年份</td></tr> <tr><td><code>%y</code></td> <td>两位数字表示的年份</td></tr></tbody></table> <h3 id="日期增加或者减去一个时间间隔"><a href="#日期增加或者减去一个时间间隔" class="header-anchor">#</a> 日期增加或者减去一个时间间隔</h3> <ul><li><p><code>date_add()</code></p></li> <li><p><code>date_sub()</code></p></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">set</span> <span class="token variable">@dt</span> <span class="token operator">=</span> <span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> date_add<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">,</span> <span class="token keyword">interval</span> <span class="token number">1</span> <span class="token keyword">day</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- add 1 day</span>
<span class="token keyword">select</span> date_add<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">,</span> <span class="token keyword">interval</span> <span class="token number">1</span> <span class="token keyword">hour</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- add 1 hour</span>
<span class="token keyword">select</span> date_add<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">,</span> <span class="token keyword">interval</span> <span class="token number">1</span> <span class="token keyword">minute</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- ...</span>
<span class="token keyword">select</span> date_add<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">,</span> <span class="token keyword">interval</span> <span class="token number">1</span> <span class="token keyword">second</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> date_add<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">,</span> <span class="token keyword">interval</span> <span class="token number">1</span> microsecond<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> date_add<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">,</span> <span class="token keyword">interval</span> <span class="token number">1</span> week<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> date_add<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">,</span> <span class="token keyword">interval</span> <span class="token number">1</span> <span class="token keyword">month</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> date_add<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">,</span> <span class="token keyword">interval</span> <span class="token number">1</span> quarter<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> date_add<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">,</span> <span class="token keyword">interval</span> <span class="token number">1</span> <span class="token keyword">year</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> date_add<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">,</span> <span class="token keyword">interval</span> <span class="token operator">-</span><span class="token number">1</span> <span class="token keyword">day</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- sub 1 day</span>
</code></pre></div><h3 id="日期时间相减"><a href="#日期时间相减" class="header-anchor">#</a> 日期时间相减</h3> <blockquote><p>两个日期相减 date1 - date2，返回天数。</p></blockquote> <blockquote><p>注意：<code>timediff(time1,time2)</code> 函数的两个参数类型必须相同。</p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">select</span> datediff<span class="token punctuation">(</span><span class="token string">'2008-08-08'</span><span class="token punctuation">,</span> <span class="token string">'2008-08-01'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 7</span>
<span class="token keyword">select</span> datediff<span class="token punctuation">(</span><span class="token string">'2008-08-01'</span><span class="token punctuation">,</span> <span class="token string">'2008-08-08'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- -7</span>
MySQL timediff<span class="token punctuation">(</span>time1<span class="token punctuation">,</span>time2<span class="token punctuation">)</span>：两个日期相减 time1 <span class="token operator">-</span> time2，返回 <span class="token keyword">time</span> 差值。
<span class="token keyword">select</span> timediff<span class="token punctuation">(</span><span class="token string">'2008-08-08 08:08:08'</span><span class="token punctuation">,</span> <span class="token string">'2008-08-08 00:00:00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 08:08:08</span>
<span class="token keyword">select</span> timediff<span class="token punctuation">(</span><span class="token string">'08:08:08'</span><span class="token punctuation">,</span> <span class="token string">'00:00:00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 08:08:08</span>
</code></pre></div><h3 id="时间戳"><a href="#时间戳" class="header-anchor">#</a> 时间戳</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">timestamp</span><span class="token punctuation">(</span><span class="token keyword">date</span><span class="token punctuation">)</span> <span class="token comment">-- date to timestamp</span>
<span class="token keyword">timestamp</span><span class="token punctuation">(</span>dt<span class="token punctuation">,</span><span class="token keyword">time</span><span class="token punctuation">)</span> <span class="token comment">-- dt + time</span>
timestampadd<span class="token punctuation">(</span>unit<span class="token punctuation">,</span><span class="token keyword">interval</span><span class="token punctuation">,</span>datetime_expr<span class="token punctuation">)</span> <span class="token comment">--</span>
timestampdiff<span class="token punctuation">(</span>unit<span class="token punctuation">,</span>datetime_expr1<span class="token punctuation">,</span>datetime_expr2<span class="token punctuation">)</span> <span class="token comment">--</span>

<span class="token keyword">select</span> <span class="token keyword">timestamp</span><span class="token punctuation">(</span><span class="token string">'2008-08-08'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 2008-08-08 00:00:00</span>
<span class="token keyword">select</span> <span class="token keyword">timestamp</span><span class="token punctuation">(</span><span class="token string">'2008-08-08 08:00:00'</span><span class="token punctuation">,</span> <span class="token string">'01:01:01'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 2008-08-08 09:01:01</span>
<span class="token keyword">select</span> <span class="token keyword">timestamp</span><span class="token punctuation">(</span><span class="token string">'2008-08-08 08:00:00'</span><span class="token punctuation">,</span> <span class="token string">'10 01:01:01'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 2008-08-18 09:01:01</span>
<span class="token keyword">select</span> timestampadd<span class="token punctuation">(</span><span class="token keyword">day</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'2008-08-08 08:00:00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 2008-08-09 08:00:00</span>
<span class="token keyword">select</span> date_add<span class="token punctuation">(</span><span class="token string">'2008-08-08 08:00:00'</span><span class="token punctuation">,</span> <span class="token keyword">interval</span> <span class="token number">1</span> <span class="token keyword">day</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 2008-08-09 08:00:00</span>
MySQL timestampadd<span class="token punctuation">(</span><span class="token punctuation">)</span> 函数类似于 date_add<span class="token punctuation">(</span><span class="token punctuation">)</span>。
<span class="token keyword">select</span> timestampdiff<span class="token punctuation">(</span><span class="token keyword">year</span><span class="token punctuation">,</span><span class="token string">'2002-05-01'</span><span class="token punctuation">,</span><span class="token string">'2001-01-01'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- -1</span>
<span class="token keyword">select</span> timestampdiff<span class="token punctuation">(</span><span class="token keyword">day</span> <span class="token punctuation">,</span><span class="token string">'2002-05-01'</span><span class="token punctuation">,</span><span class="token string">'2001-01-01'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- -485</span>
<span class="token keyword">select</span> timestampdiff<span class="token punctuation">(</span><span class="token keyword">hour</span><span class="token punctuation">,</span><span class="token string">'2008-08-08 12:00:00'</span><span class="token punctuation">,</span><span class="token string">'2008-08-08 00:00:00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- -12</span>
<span class="token keyword">select</span> datediff<span class="token punctuation">(</span><span class="token string">'2008-08-08 12:00:00'</span><span class="token punctuation">,</span> <span class="token string">'2008-08-01 00:00:00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 7</span>
</code></pre></div><h3 id="日期时间选取"><a href="#日期时间选取" class="header-anchor">#</a> 日期时间选取</h3> <blockquote><p>日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒</p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">set</span> <span class="token variable">@dt</span> <span class="token operator">=</span> <span class="token string">'2008-09-10 07:15:30.123456'</span><span class="token punctuation">;</span>
 
<span class="token keyword">select</span> <span class="token keyword">date</span><span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 2008-09-10</span>
<span class="token keyword">select</span> <span class="token keyword">time</span><span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 07:15:30.123456</span>
<span class="token keyword">select</span> <span class="token keyword">year</span><span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 2008</span>
<span class="token keyword">select</span> quarter<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 3</span>
<span class="token keyword">select</span> <span class="token keyword">month</span><span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 9</span>
<span class="token keyword">select</span> week<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 36</span>
<span class="token keyword">select</span> <span class="token keyword">day</span><span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 10</span>
<span class="token keyword">select</span> <span class="token keyword">hour</span><span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 7</span>
<span class="token keyword">select</span> <span class="token keyword">minute</span><span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 15</span>
<span class="token keyword">select</span> <span class="token keyword">second</span><span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 30</span>
<span class="token keyword">select</span> microsecond<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 123456</span>
</code></pre></div><ul><li><code>Extract()</code></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">set</span> <span class="token variable">@dt</span> <span class="token operator">=</span> <span class="token string">'2008-09-10 07:15:30.123456'</span><span class="token punctuation">;</span>
 
<span class="token keyword">select</span> extract<span class="token punctuation">(</span><span class="token keyword">year</span> <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 2008</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>quarter <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 3</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span><span class="token keyword">month</span> <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 9</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>week <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 36</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span><span class="token keyword">day</span> <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 10</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span><span class="token keyword">hour</span> <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 7</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span><span class="token keyword">minute</span> <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 15</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span><span class="token keyword">second</span> <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 30</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>microsecond <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 123456select extract(year_month from @dt); -- 200809</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>day_hour <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 1007</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>day_minute <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 100715</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>day_second <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 10071530</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>day_microsecond <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 10071530123456</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>hour_minute <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 715</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>hour_second <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 71530</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>hour_microsecond <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 71530123456</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>minute_second <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 1530</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>minute_microsecond <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 1530123456</span>
<span class="token keyword">select</span> extract<span class="token punctuation">(</span>second_microsecond <span class="token keyword">from</span> <span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 30123456</span>
</code></pre></div><ul><li><code>dayofweek()</code> 返回日期参数，在一周中的位置。</li> <li><code>dayofmonth()</code> 返回日期参数，在一月中的位置。</li> <li><code>dayofyear()</code> 返回日期参数，在一年中的位置。</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">set</span> <span class="token variable">@dt</span> <span class="token operator">=</span> <span class="token string">'2008-08-08'</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> dayofweek<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 6</span>
<span class="token keyword">select</span> dayofmonth<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 8</span>
<span class="token keyword">select</span> dayofyear<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- 221</span>
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment"># 获取当前日期</span>
<span class="token keyword">SELECT</span> CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># 获取当月最后一天</span>
<span class="token keyword">SELECT</span> LAST_DAY<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># 获取当月第一天</span>
<span class="token keyword">SELECT</span> DATE_ADD<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token operator">-</span><span class="token keyword">DAY</span><span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token number">1</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># 获取下个月的第一天</span>
<span class="token keyword">SELECT</span> DATE_ADD<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token keyword">DAY</span><span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">MONTH</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># 获取上个月的今天</span>
<span class="token keyword">SELECT</span> DATE_SUB<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">MONTH</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># MySQL last_day() 函数非常有用，比如我想得到当前月份中有多少天，可以这样来计算：</span>
<span class="token keyword">SELECT</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">DAY</span><span class="token punctuation">(</span>LAST_DAY<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> days<span class="token punctuation">;</span>
<span class="token comment"># 或者</span>
<span class="token keyword">SELECT</span> <span class="token keyword">DAY</span><span class="token punctuation">(</span>LAST_DAY<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment"># 或者</span>
<span class="token keyword">SELECT</span> DATEDIFF<span class="token punctuation">(</span>DATE_ADD<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token keyword">DAY</span><span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">MONTH</span> <span class="token punctuation">)</span><span class="token punctuation">,</span>DATE_ADD<span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token operator">-</span><span class="token keyword">DAY</span><span class="token punctuation">(</span>CURDATE<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">+</span><span class="token number">1</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> days <span class="token keyword">FROM</span> DUAL<span class="token punctuation">;</span>
</code></pre></div><div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">/*上个月今天的当前时间*/</span>
<span class="token keyword">SELECT</span> DATE_SUB<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">MONTH</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">/*上个月今天的当前时间（时间戳）*/</span>
<span class="token keyword">SELECT</span> UNIX_TIMESTAMP<span class="token punctuation">(</span>DATE_SUB<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">MONTH</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">/*上个月的第一天*/</span>
<span class="token keyword">SELECT</span> DATE_SUB<span class="token punctuation">(</span>DATE_SUB<span class="token punctuation">(</span>DATE_FORMAT<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'%y-%m-%d 00:00:00'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> EXTRACT<span class="token punctuation">(</span><span class="token keyword">DAY</span> <span class="token keyword">FROM</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token number">1</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">MONTH</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">/*上个月的第一天（时间戳）*/</span>
<span class="token keyword">SELECT</span> UNIX_TIMESTAMP<span class="token punctuation">(</span>DATE_SUB<span class="token punctuation">(</span>DATE_SUB<span class="token punctuation">(</span>DATE_FORMAT<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'%y-%m-%d 00:00:00'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> EXTRACT<span class="token punctuation">(</span><span class="token keyword">DAY</span> <span class="token keyword">FROM</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token number">1</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">MONTH</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">/*上个月的第一天：*/</span>
<span class="token keyword">SELECT</span> DATE_SUB<span class="token punctuation">(</span>DATE_SUB<span class="token punctuation">(</span>DATE_FORMAT<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'%y-%m-%d'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> EXTRACT<span class="token punctuation">(</span><span class="token keyword">DAY</span> <span class="token keyword">FROM</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token number">1</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">1</span> <span class="token keyword">MONTH</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">/*上个月的最后一天：*/</span>
<span class="token keyword">SELECT</span> DATE_SUB<span class="token punctuation">(</span>DATE_SUB<span class="token punctuation">(</span>DATE_FORMAT<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'%y-%m-%d'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> EXTRACT<span class="token punctuation">(</span><span class="token keyword">DAY</span> <span class="token keyword">FROM</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">0</span> <span class="token keyword">MONTH</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">DATE</span><span class="token punctuation">;</span>

<span class="token comment">/*这个月的第一天：*/</span>
<span class="token keyword">SELECT</span> DATE_SUB<span class="token punctuation">(</span>DATE_SUB<span class="token punctuation">(</span>DATE_FORMAT<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'%y-%m-%d'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> EXTRACT<span class="token punctuation">(</span><span class="token keyword">DAY</span> <span class="token keyword">FROM</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token number">1</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> <span class="token number">0</span> <span class="token keyword">MONTH</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">/*这个月的最后一天：*/</span>
<span class="token keyword">SELECT</span> DATE_SUB<span class="token punctuation">(</span>DATE_SUB<span class="token punctuation">(</span>DATE_FORMAT<span class="token punctuation">(</span><span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token string">'%y-%m-%d'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span> EXTRACT<span class="token punctuation">(</span><span class="token keyword">DAY</span> <span class="token keyword">FROM</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">DAY</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token keyword">INTERVAL</span><span class="token operator">-</span><span class="token number">1</span> <span class="token keyword">MONTH</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">DATE</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="返回星期和月份名称"><a href="#返回星期和月份名称" class="header-anchor">#</a> 返回星期和月份名称</h3> <ul><li><code>dayname()</code></li> <li><code>monthname()</code></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">set</span> <span class="token variable">@dt</span> <span class="token operator">=</span> <span class="token string">'2008-08-08'</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> dayname<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- Friday</span>
<span class="token keyword">select</span> monthname<span class="token punctuation">(</span><span class="token variable">@dt</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">-- August</span>
</code></pre></div><h2 id="用户管理"><a href="#用户管理" class="header-anchor">#</a> 用户管理</h2> <h3 id="查看授权"><a href="#查看授权" class="header-anchor">#</a> 查看授权</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment"># 查看root用户</span>
<span class="token keyword">SHOW</span> GRANTS<span class="token punctuation">;</span>
<span class="token comment"># 查看指定用户</span>
<span class="token keyword">SHOW</span> GRANTS <span class="token keyword">FOR</span> <span class="token string">'root'</span><span class="token variable">@'localhost'</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="创建用户并授权"><a href="#创建用户并授权" class="header-anchor">#</a> 创建用户并授权</h3> <blockquote><p>以root用户登录到数据库后进行用户创建</p></blockquote> <p><strong><code>CREATE</code>创建的用户需要手动授权</strong></p> <ul><li><code>username</code> 将要创建的用户名；</li> <li><code>host</code> 指定该用户在哪个主机上可以登录，<code>%</code> 表示在任何一台电脑上都可以登录</li> <li><code>password</code> 该用户的登录密码，密码可以为空，若为空则该用户可以不需要密码登录服务器</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment"># 创建本地登录账户</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'admin'</span><span class="token variable">@'localhost'</span> IDENTIFIED <span class="token keyword">BY</span> <span class="token string">'密码'</span><span class="token punctuation">;</span>
<span class="token comment"># 创建所有主机可登录账户</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'admin'</span><span class="token variable">@'%'</span> IDENTIFIED <span class="token keyword">BY</span> <span class="token string">'密码'</span><span class="token punctuation">;</span>
</code></pre></div><p><strong><code>GRANT</code>创建用户或授权</strong></p> <ul><li><code>GRANT</code> 用户存在时会进行授权，用户不存在时，创建用户并授权。
<ul><li>8.0 必须先创建其他用户再授权（不能授权给自己），否则会报错<code>You are not allowed to create a user with GRANT</code></li></ul></li> <li><code>WITH GRANT OPTION</code> 这个选项表示该用户可以将自己拥有的权限授权给别人</li> <li><code>PRIVILEGES</code> 用户的操作权限，如<code>INSERT</code>,<code>DELETE</code>,<code>UPDATE</code>,<code>SELECT</code>等。所有权限则使用<code>ALL PRIVILEGES</code>。</li> <li><code>database.table</code> 数据库名.表名，所有数据库和表用<code>*.*</code>表示。用<code>`<code>（反引号）包裹。</code></code></li> <li><code>IDENTIFIED BY</code> 指定密码，如果不带此属性会导致创建的用户无法远程连接，虽然从<code>mysql.user</code>查出<code>host</code>为<code>%</code> <ul><li>8.0 使用此语句会报错</li></ul></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment"># 创建只读账号</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> <span class="token keyword">database</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'reader'</span><span class="token variable">@'%'</span> IDENTIFIED <span class="token keyword">BY</span> <span class="token string">&quot;密码&quot;</span><span class="token punctuation">;</span>
<span class="token comment"># 增删改查账号</span>
<span class="token keyword">GRANT</span> <span class="token keyword">INSERT</span><span class="token punctuation">,</span><span class="token keyword">DELETE</span><span class="token punctuation">,</span><span class="token keyword">UPDATE</span><span class="token punctuation">,</span><span class="token keyword">SELECT</span> <span class="token keyword">ON</span> <span class="token keyword">database</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'writer'</span><span class="token variable">@'%'</span> IDENTIFIED <span class="token keyword">BY</span> <span class="token string">&quot;密码&quot;</span><span class="token punctuation">;</span>
<span class="token comment"># 创建拥有所有权限的账户，并且所有主机可登录</span>
<span class="token keyword">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">PRIVILEGES</span> <span class="token keyword">ON</span> <span class="token keyword">database</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'root'</span><span class="token variable">@'%'</span> IDENTIFIED <span class="token keyword">BY</span> <span class="token string">'密码'</span> <span class="token keyword">WITH</span> <span class="token keyword">GRANT</span> <span class="token keyword">OPTION</span><span class="token punctuation">;</span>
<span class="token comment"># MySQL 8.0 为用户授权所有权限</span>
<span class="token keyword">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">PRIVILEGES</span> <span class="token keyword">ON</span> <span class="token keyword">database</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'root'</span><span class="token variable">@'%'</span> <span class="token keyword">WITH</span> <span class="token keyword">GRANT</span> <span class="token keyword">OPTION</span><span class="token punctuation">;</span>

<span class="token comment"># 刷新权限</span>
FLUSH <span class="token keyword">PRIVILEGES</span><span class="token punctuation">;</span>
<span class="token comment"># 查看用户信息</span>
<span class="token keyword">select</span> <span class="token keyword">user</span><span class="token punctuation">,</span>host <span class="token keyword">from</span> mysql<span class="token punctuation">.</span><span class="token keyword">user</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="撤销用户权限"><a href="#撤销用户权限" class="header-anchor">#</a> 撤销用户权限</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">REVOKE</span> privilege <span class="token keyword">ON</span> databasename<span class="token punctuation">.</span>tablename <span class="token keyword">FROM</span> <span class="token string">'username'</span><span class="token variable">@'host'</span><span class="token punctuation">;</span>
<span class="token comment"># 刷新权限</span>
FLUSH <span class="token keyword">PRIVILEGES</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="删除账户及权限"><a href="#删除账户及权限" class="header-anchor">#</a> 删除账户及权限</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">DROP</span> <span class="token keyword">USER</span> <span class="token string">'username'</span><span class="token variable">@'host'</span><span class="token punctuation">;</span>
<span class="token comment"># 刷新权限</span>
FLUSH <span class="token keyword">PRIVILEGES</span><span class="token punctuation">;</span>
</code></pre></div><h2 id="安装后修改密码"><a href="#安装后修改密码" class="header-anchor">#</a> 安装后修改密码</h2> <h3 id="使用默认密码进入修改密码"><a href="#使用默认密码进入修改密码" class="header-anchor">#</a> 使用默认密码进入修改密码</h3> <ul><li>查看root账号的默认密码</li></ul> <blockquote><p>mysql5.7安装完成之后，在<code>/var/log/mysqld.log</code>文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录mysql。</p></blockquote> <div class="language-bash extra-class"><pre class="language-bash"><code><span class="token comment"># 其中`root@localhost:`后面部分就是默认密码</span>
<span class="token function">grep</span> <span class="token string">'temporary password'</span> /var/log/mysqld.log
</code></pre></div><ul><li>修改密码</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">ALTER</span> <span class="token keyword">USER</span> <span class="token string">'root'</span><span class="token variable">@'localhost'</span> IDENTIFIED <span class="token keyword">BY</span> <span class="token string">'新密码'</span><span class="token punctuation">;</span>
<span class="token comment"># 8.0</span>
<span class="token keyword">ALTER</span> <span class="token keyword">USER</span> <span class="token string">'root'</span><span class="token variable">@'localhost'</span> IDENTIFIED <span class="token keyword">WITH</span> mysql_native_password <span class="token keyword">BY</span> <span class="token string">'password'</span><span class="token punctuation">;</span>
<span class="token comment"># 刷新权限</span>
FLUSH <span class="token keyword">PRIVILEGES</span><span class="token punctuation">;</span>
</code></pre></div><ul><li>如果出现以下错误，就说明密码强度不够：</li></ul> <blockquote><p><code>ERROR 1819 (HY000): Your password does not satisfy the current policy requirements</code></p></blockquote> <blockquote><p>需要修改以下两个参数，然后再次修改密码</p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">set</span> <span class="token keyword">global</span> validate_password_policy<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">set</span> <span class="token keyword">global</span> validate_password_length<span class="token operator">=</span>自己想要的密码长度<span class="token punctuation">;</span>
</code></pre></div><h3 id="修改配置修改密码"><a href="#修改配置修改密码" class="header-anchor">#</a> 修改配置修改密码</h3> <ul><li>修改<code>/etc/my.cnf</code></li></ul> <blockquote><p>在<code>[mysqld]</code>小节下添加一行,修改密码完成后需要删除此行</p></blockquote> <div class="language-bash extra-class"><pre class="language-bash"><code>skip-grant-tables <span class="token operator">=</span> <span class="token number">1</span>
</code></pre></div><blockquote><p>这一行配置让<code>mysqld</code>启动时不对密码进行验证</p></blockquote> <ul><li>重启</li></ul> <div class="language-bash extra-class"><pre class="language-bash"><code>systemctl restart mysqld
</code></pre></div><ul><li>使用<code>root</code>用户登录</li></ul> <div class="language-bash extra-class"><pre class="language-bash"><code>mysql -uroot
</code></pre></div><ul><li>切换到<code>mysql</code>数据库</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">use</span> mysql
</code></pre></div><ul><li>更新<code>user</code>表</li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">update</span> <span class="token keyword">user</span> <span class="token keyword">set</span> authentication_string <span class="token operator">=</span> password<span class="token punctuation">(</span><span class="token string">'新密码'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>password_expired <span class="token operator">=</span> <span class="token string">'N'</span><span class="token punctuation">,</span> password_last_changed <span class="token operator">=</span> <span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">where</span> <span class="token keyword">user</span> <span class="token operator">=</span> <span class="token string">'root'</span><span class="token punctuation">;</span>
</code></pre></div><blockquote><p>在<code>5.7</code>之前的版本密码字段的字段名是<code>password</code>，<code>5.7</code>版本改为了<code>authentication_string</code></p></blockquote> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment"># 使用set设置密码</span>
<span class="token keyword">set</span> password <span class="token keyword">for</span> <span class="token string">'root'</span><span class="token variable">@'localhost'</span><span class="token operator">=</span>password<span class="token punctuation">(</span><span class="token string">'123456'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment"># 或者使用update修改</span>
<span class="token keyword">update</span> <span class="token keyword">user</span> <span class="token keyword">set</span> password<span class="token operator">=</span>PASSWORD<span class="token punctuation">(</span><span class="token string">&quot;123456&quot;</span><span class="token punctuation">)</span> <span class="token keyword">where</span> <span class="token keyword">user</span><span class="token operator">=</span><span class="token string">'root'</span><span class="token punctuation">;</span>
</code></pre></div></div> <footer class="page-edit"><div class="edit-link"><a href="https://github.com/woytu/notes-vuepress/edit/master//SQL/MySQL笔记.md" target="_blank" rel="noopener noreferrer">在 GitHub 上编辑此页</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="last-updated"><span class="prefix">上次更新:</span> <span class="time">2021-01-18 19:24:13</span></div></footer> <div class="page-nav"><p class="inner"><span class="prev">
      ←
      <a href="/SQL/MySQL常见问题.html" class="prev">
        MySQL常见问题
      </a></span> <span class="next"><a href="/SQL/mysql-udf安装.html">
        mysql-udf安装
      </a>
      →
    </span></p></div> </main></div><div class="global-ui"><!----></div></div>
    <script src="/assets/js/app.269009d6.js" defer></script><script src="/assets/js/3.974b9720.js" defer></script><script src="/assets/js/53.3a9bd5fb.js" defer></script>
  </body>
</html>
